package com.yidu.oracle.dao;

import com.yidu.oracle.Tools.DBUtil;
import com.yidu.oracle.model.Message;
import oracle.jdbc.OracleTypes;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName wjy
 * @Description
 * @Author86157
 * @Date 2021/3/19 14:47
 * @Version 1.0
 */
public class MessageDao {
    /**
     * 查询
     * @return
     */
    public List<Message> select() {
        Connection connection=null;
        CallableStatement callableStatement=null;
        ResultSet resultSet=null;
        List<Message> list=new ArrayList<>();
        try {
            connection= DBUtil.getConnection();
            String sql="{call message_select(?)}";
            callableStatement=connection.prepareCall(sql);
            //注册
            callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
            //执行
            callableStatement.execute();
            //取出结果集合
            resultSet=(ResultSet)callableStatement.getObject(1);
            while (resultSet.next()){
                Message message=new Message();
                message.setId(resultSet.getInt("mid"));
                message.setName(resultSet.getString("mname"));
                message.setContent(resultSet.getString("mcontent"));
                message.setTitle(resultSet.getString("mtitle"));
                message.setShjijian(resultSet.getString("mdate"));
                list.add(message);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 增加
     * @param message
     * @return
     */
    public int add(Message message) {
        Connection connection=null;
        CallableStatement callableStatement=null;
        int rows=0;
        try {
            connection=DBUtil.getConnection();
            String sql="{call message_insert(?,?,?,?,?) }";
            callableStatement=connection.prepareCall(sql);
            callableStatement.setInt(1,message.getId());
            callableStatement.setString(2,message.getName());
            callableStatement.setString(3,message.getTitle());
            callableStatement.setString(4,message.getContent());
            callableStatement.setString(5,message.getShjijian());
            //执行
            callableStatement.execute();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }
}
